package com.cxd.base.util;

import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.cxd.base.common.exception.CxdException;
import com.cxd.base.common.exception.ExceptionEnum;
import com.cxd.base.mapper.read.RManageMapper;
import com.cxd.base.mapper.write.WManageMapper;
import com.cxd.base.util.date.DateUtil;
import com.cxd.base.util.wtj.BUtils;
import com.cxd.base.util.wtj.SUtils;

/**
 * @author wtj
 * @date 2015年12月1日 下午4:16:20
 * @company CXD
 * @version [Copyright (c) 2015 V001]
 */
@Service("db")
public class BeanTools
{
	private static final String DATABASE_TABLE_NAME = "database_table_name";
	private static final String TABLE_FIELDS = "database_table_fields";
	
	@Resource
	public RManageMapper rmanageMapper;
	
	@Resource
	public WManageMapper wmanageMapper;
	
	
	public void setwmanageMapper(WManageMapper wmanageMapper) {
		this.wmanageMapper = wmanageMapper;
	}

	public void setrmanageMapper(RManageMapper rmanageMapper) {
		this.rmanageMapper = rmanageMapper;
	}

	private static final Logger logger = Logger.getLogger(BeanTools.class);
	
	/**
	 * 字段唯一值判断 entity 实体 file 判断的字段
	 */
	public boolean onlyRestrain(Object entity, String file)
	{
		Map<String,Object> columns = getFieldByBean(entity);
		String tableName = columns.get(DATABASE_TABLE_NAME).toString();
		String filename = columns.get(file).toString();
		String fileValue = "";
		try {
			fileValue = PropertyUtils.getProperty(entity, file).toString();
		} catch (IllegalAccessException e) {
			throw new  CxdException(ExceptionEnum.IllegalAccessException.getExceDes());
		} catch (InvocationTargetException e) {
			throw new  CxdException(ExceptionEnum.InvocationTargetException.getExceDes());
		} catch (NoSuchMethodException e) {
			// 不处理
		}
		List<Map<String,Object>> li = rmanageMapper.queryForList("SELECT "+getTableFields(tableName)+" FROM " + tableName + " WHERE " + BeanTools.filterSqlPara(filename) + "='" + BeanTools.filterSqlPara(fileValue) + "'");
		if (li.size() > 0)
		{
			return false;
		}
		return true;
	}
	public void getKey(StringBuilder fields,String fieldType,String fieldValue){
		fields.append(fieldValue).append(",");
	}
	
	public String getAddSql(Object entity) throws CxdException
	{
		StringBuilder sql = new StringBuilder("INSERT INTO ");
		StringBuilder values = new StringBuilder();
		StringBuilder fields = new StringBuilder();
		
		Map<String,Object> columns = getFieldByBean(entity);
		String tableName = columns.get(DATABASE_TABLE_NAME).toString();
		sql.append(tableName).append(" (");
		Set<String> set = columns.keySet();
		Iterator<String> iterator = set.iterator();
		
		while (iterator.hasNext())
		{
			String beanField = (String) iterator.next();
			if (!beanField.equals(DATABASE_TABLE_NAME))
			{
				String fieldName = (String) columns.get(beanField);
				try
				{
					Object value = null ;
					if(TABLE_FIELDS.equalsIgnoreCase(beanField)){
						continue;
					}
					try {
						if("ctime".equalsIgnoreCase(beanField)){
							value = new Date();
						}else{
							value = PropertyUtils.getProperty(entity, beanField);
						}
					} catch (NullPointerException e) {
						continue;
					} 
					
					if (fieldName.equalsIgnoreCase("id"))
					{
						fields.append(fieldName).append(",");
						if(value==null){
							String id = SequenceManager.getNextId();
							values.append("\"").append(id).append("\",");
							PropertyUtils.setProperty(entity, "id", id);
						}else{
							values.append("\"").append(value).append("\",");
							PropertyUtils.setProperty(entity, "id", value);
						}

					}
					else
					{
							if (value != null)
							{
								
								// System.out.println(PropertyUtils.getPropertyType(entity, beanField)+"---"+Date.class.toString());
								if(PropertyUtils.getPropertyType(entity, beanField).toString().equals(Date.class.toString())){
									//values.append("'").append(DateUtil.getFullString((Date) value)).append("',");
									values.append("now(),");
								}else{
									values.append("\"").append(value.toString()).append("\",");
								}
								fields.append(fieldName).append(",");
							}
					}
				}
				catch (InvocationTargetException e)
				{
					logger.error("", e);
					throw new CxdException("InvocationTargetException");
				}
				catch (NoSuchMethodException e)
				{
//					logger.error("", e);
					continue;
//					throw new CxdException("NoSuchMethodException");
				}
				catch (SecurityException e)
				{
					logger.error("", e);
					throw new CxdException("SecurityException");
				}
				catch (IllegalArgumentException e)
				{
					logger.error("", e);
					throw new CxdException("IllegalArgumentException");
				}
				catch (IllegalAccessException e)
				{
					logger.error("", e);
					throw new CxdException("IllegalAccessException");
				}
			}
		}
		String fieldValue = fields.toString();
		sql.append( fieldValue.substring(0, fieldValue.length() - 1) )
			.append(") VALUES (")
			.append( values.toString().substring(0, values.length() - 1) )
			.append(")");
		return sql.toString();
	}
	
	public int add(Object entity){
		String sql = getAddSql(entity);
		int count = wmanageMapper.add(sql);
		return count;
	}
	public int addLog(Object entity){
		String sql = getAddSql(entity);
		int count = wmanageMapper.add(sql);
		return count;
	}
	
//	public int addLog(Object entity,IUser iuser){
//		
//	}
	
//	@SuppressWarnings("unchecked")
//	private Map<String,Object> getTableMap(Object entity){
//		try {
//			Class<? extends Object> clazz = entity.getClass();
//			Object bean = clazz.getConstructor(new Class[] {}).newInstance(new Object[] {});
//			Method setMethod = clazz.getMethod("getTableMap", new Class[] {});
//			Map<String,Object> columns = (Map<String,Object>)setMethod.invoke(bean);
//			return columns;
//		} catch (NoSuchMethodException e) {
//			e.printStackTrace();
//		} catch (SecurityException e1) {
//			e1.printStackTrace();
//		} catch (IllegalAccessException e) {
//			e.printStackTrace();
//		} catch (IllegalArgumentException e) {
//			e.printStackTrace();
//		} catch (InvocationTargetException e) {
//			e.printStackTrace();
//		}catch (InstantiationException e) {
//			e.printStackTrace();
//		}
//		return null;
//	}
	/**
	 * 修改方法
	 * @author WTJ
	 * @date 2015-5-13 上午7:51:30 
	 * @sortm entity
	 * @return
	 * @throws CxdException
	 * @see [类、类#方法、类#成员]
	 */
	public int modify(Object entity) throws CxdException {

		Map<String,Object> columns = getFieldByBean(entity);
		
		Set<String> set = columns.keySet();
		Iterator<String> iterator = set.iterator();
		String setStr = "";
		String whereStr = "";
		while (iterator.hasNext())
		{
			String beanField = (String) iterator.next();
			if (!beanField.equals(DATABASE_TABLE_NAME))
			{
				String tableField = (String) columns.get(beanField);
				try
				{
					String type = "";
					try {
						type = PropertyUtils.getPropertyType(entity, beanField).toString();
					} catch (NullPointerException e) {
						continue;
					}
					Object value = PropertyUtils.getProperty(entity, beanField);
					if(value == null || value.equals("0")){
						continue;
					}
					if (!tableField.equalsIgnoreCase("id"))
					{
						if (type.equals(Integer.class.toString()) || type.equals(int.class.toString()))
						{
								setStr += tableField + "=";
								setStr += value.toString() + ",";
						}
						else if (type.equals(Long.class.toString()) || type.equals(long.class.toString()))
						{
								setStr += tableField + "=";
								setStr += value.toString() + ",";
						}
						else if (type.equals(Float.class.toString())||type.equals(float.class.toString()))
						{
								setStr += tableField + "=";
								setStr += value.toString() + ",";
						}
						else if (type.equals(String.class.toString()))
						{
							if (value != null)
							{
								setStr += tableField + "=";
								setStr += "'" + value.toString() + "',";
							}
						}
						else if (type.equals(Date.class.toString()))
						{
							if (value != null)
							{
								setStr += tableField + "=";
								String tmp = DateUtil.getFullString((Date) value);
								setStr += "'" + tmp + "',";
							}
						}
					}
					else if (value != null)
					{
						whereStr = " WHERE ID = '" + value.toString() + "'";
					}
				}
				catch (InvocationTargetException e)
				{
					throw new CxdException("InvocationTargetException");
				}
				catch (NoSuchMethodException e)
				{
					continue;
				}
				catch (SecurityException e)
				{
					throw new CxdException("SecurityException");
				}
				catch (IllegalArgumentException e)
				{
					throw new CxdException("IllegalArgumentException");
				}
				catch (IllegalAccessException e)
				{
					throw new CxdException("IllegalAccessException");
				}
			}
		}
		setStr = setStr.substring(0, setStr.length() - 1);
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(columns.get(DATABASE_TABLE_NAME)).append(" SET ");
		sql.append(setStr);
		sql.append(whereStr);
		if (sql.indexOf(" WHERE ") < 0)
		{
			throw new CxdException("sql语句中未找到where子句，可能的原因为：表中没有ID主键。");
		}
		return wmanageMapper.modify(sql.toString());
	}
	
	/**
	 * 获取删除语句
	 * @author WTJ
	 * @date 2015-5-13 上午7:54:17 
	 * @sortm tableName 表名
	 * @sortm columnName 删除条件字段名 以“,”分割 如：id,name
	 * @sortm values 删除条件value值 以“,”分割 如：1,wtj
	 * @return
	 * @see [类、类#方法、类#成员]
	 */
	public String getDelSql(String tableName, String columnName[], String values[]){
		StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName).append(" WHERE 1 = 1 ");
		for (int i = 0; i < columnName.length; i++)
		{
			sql.append(" AND ").append(columnName[i]).append(" = '").append(BeanTools.filterSqlPara(values[i])).append("' ");
		}
		return sql.toString();
	}
	/**
	 * 物理删除数据
	 * @author WTJ
	 * @date 2015-5-13 上午7:56:56 
	 * @sortm tableName 表名
	 * @sortm columnName 删除条件字段名 以“,”分割 如：id,name
	 * @sortm values 删除条件value值 以“,”分割 如：1,wtj
	 * @return
	 * @see [类、类#方法、类#成员]
	 */
	public int del(String tableName, String[] columnName, String[] values)
	{
		String sql = getDelSql(tableName,columnName,values);
		int t = wmanageMapper.del(sql);
//		if(t != 1){
//			logger.error("表"+tableName+"删除数据"+values+"异常:删除条数-"+t);
//			throw new CxdException("数据异常删除失败："+t);
//		}
		return t;
	}
	
	/**
	 * 物理删除数据并记录日志
	 * @author WTJ
	 * @date 2015-5-13 上午8:00:10 
	 * @sortm tableName 表名
	 * @sortm columnName 删除条件字段名 以“,”分割 如：id,name
	 * @sortm values 删除条件value值 以“,”分割 如：1,wtj
	 * @return
	 * @see [类、类#方法、类#成员]
	 */
//	public int delLog(TableEnum TableEnum , String[] columnName, String[] values)
//	{
//		return delLog(TableEnum.getTableName(),columnName,values);
//	}

	public int delLog(String tableName, String[] columnName, String[] values){
		String sql = getDelSql(tableName,columnName,values);
		int t = wmanageMapper.del(sql);
		/*记录日志操作*/
		
		return t;
	}
	
	private Map<String, Object> getBeanMap(String tableName,String[] columnName, Object values[]){
		if(StringUtils.isEmpty(tableName) || columnName.length==0 || columnName.length != values.length){
			logger.error("参数有问题: tableName:"+tableName+",columnName:"+columnName+",values:"+values);
			throw new CxdException("参数有问题!");
		}
		StringBuffer sb = new StringBuffer(" WHERE 1=1  ");
		for(int i=0;i<columnName.length;i++){
			sb.append(" AND ").append(BeanTools.filterSqlPara(columnName[i])).append("='").append(BeanTools.filterSqlPara(values[i])).append("'");
		}
		String sql = "SELECT "+getTableFields(tableName)+" FROM "+tableName+sb;
		List<Map<String, Object>> list = rmanageMapper.queryForList(sql);
		
		if(list.size() >1){
			logger.error("查询数据有问题："+sql+JSONArray.toJSONString(list));
			throw new CxdException("查询结果集大于1!");
		}else if(list.size() == 0){
			return null;
		}else{
			return list.get(0);
		}
	}
	
	public List<Map<String, Object>> getListMap(String tableName,String columnName, Object values){
		return getListMap(tableName,new String[]{columnName},new Object[]{values},null);
	}
	public List<Map<String, Object>> getListMap(String tableName,String[] columnName, Object values[],String sort){
		if(StringUtils.isEmpty(tableName) || columnName.length==0 || columnName.length != values.length){
			logger.error("参数有问题: tableName:"+tableName+",columnName:"+columnName+",values:"+values);
			throw new CxdException("参数有问题!");
		}
		StringBuffer sb = new StringBuffer(" WHERE 1=1  ");
		for(int i=0;i<columnName.length;i++){
			sb.append(" AND ").append(BeanTools.filterSqlPara(columnName[i])).append("='").append(BeanTools.filterSqlPara(values[i])).append("'");
		}
		if(sort == null){
			return rmanageMapper.queryForList("SELECT * FROM "+tableName+sb);
		}else{
			return rmanageMapper.queryForList("SELECT * FROM "+tableName+sb+sort);
		}
	}

	private static Map<String,Map<String,Object>> tables = new HashMap<String,Map<String,Object>>();
	
	/**
	 * 根据实体 获取对应数据库字段
	 * @author WTJ
	 * @date 2015-5-20 下午9:42:56 
	 * @sortm obj
	 * @return
	 * @see [类、类#方法、类#成员]
	 */
	public Map<String,Object> getFieldByBean(Object obj){
		String entityName = obj.getClass().getName().toString().replaceAll("Base", "");
		return getFieldByBeanName(entityName.substring(entityName.lastIndexOf(".")+1));
	}
	
	public Map<String,Object> getFieldByTableName(String tableName){
		String[] ts = StringUtils.split(tableName, "_");
		String tableName2 = "";
		for(int j=0; j<ts.length; j++){
			tableName2 += ts[j].substring(0,1).toUpperCase()+ts[j].substring(1).toLowerCase();
		}
		return getFieldByBeanName(tableName2);
	}
	
	/**
	 * 根据表名获取对应字段
	 * <一句话功能简述>
	 * <功能详细描述>
	 * @author WTJ
	 * @date 2015-5-22 下午8:20:42 
	 * @param tableName
	 * @return
	 * @see [类、类#方法、类#成员]
	 */
	public String getTableFields(String tableName){
		return getFieldByTableName(tableName).get(TABLE_FIELDS).toString();
	}
	/**
	 * 获取所有表字段
	 * <一句话功能简述>
	 * <功能详细描述>
	 * @author WTJ
	 * @date 2015-5-20 下午9:42:03 
	 * @sortm beanName
	 * @return
	 * @see [类、类#方法、类#成员]
	 */
	public Map<String,Object> getFieldByBeanName(String beanName){
		if(tables.get(beanName) == null){
			StringBuffer sb = new StringBuffer();
			// 获取表名
			String tableName = SUtils.getHumpToUpperCase(beanName);
			// 获取表的所有字段
			List<Map<String,Object>> columns = getColumnList(tableName);
			Map<String,Object> m = new HashMap<String,Object>();
			m.put(DATABASE_TABLE_NAME, tableName);
			for(int j=0; j<columns.size(); j++){
				Map<String,Object> column = columns.get(j);
				String fieldUpper = column.get("Field").toString().toUpperCase();
				String fieldLower = fieldUpper.toLowerCase();
				if(j>0){
					sb.append(",");
				}
				sb.append("`"+fieldUpper+"`");
				m.put(SUtils.getStrToHump(fieldLower), fieldLower);
			}
			m.put("database_table_fields", sb.toString());
			tables.put(beanName, m);
			return m;
		}else{
			return tables.get(beanName);
		}
	};
	
	private List<Map<String,Object>> getColumnList(String tableName) {
		List<Map<String, Object>> list = rmanageMapper.queryForList("SHOW FULL fields from " + tableName);
		List<Map<String, Object>> list1 = rmanageMapper.queryForList("SELECT  COLUMN_NAME ,COLUMN_COMMENT   from  Information_schema.columns   where table_Name='"+ tableName+"'");
		Map<String,Object> mm = new HashMap<String,Object>();
		for(Map<String, Object> m:list1){
			mm.put(m.get("COLUMN_NAME").toString(), m.get("COLUMN_COMMENT"));
		}
		for (Map<String,Object> m:list) {
			String field = m.get("Field").toString();
			//String fieldType =  m.get("Type").toString();
			m.put("COLUMN_COMMENT", mm.get(field).toString());
		}
		return list;
	}
	
	public <T> T queryBean(String tableName ,String column,Object valuse,Class<T> clazz)throws CxdException{
		return queryBean(tableName,new String[]{column},new Object[]{valuse},clazz);
	}
	public <T> T queryBean(String tableName,String[] columns ,Object[] values,Class<T> clazz)throws  CxdException{
		Map<String, Object> map = this.getBeanMap(tableName,columns,values);
		return BUtils.tableMapToBean(map,clazz);
	}
	public <T> T queryBean(String sql,Class<T> clazz)throws CxdException{
		List<Map<String,Object>> listMap = rmanageMapper.queryForList(sql);
		if(listMap.size()==0){
			return null;
		}else{
			return BUtils.tableMapToBean(listMap.get(0),clazz);
		}
	}
	/**
	 * 获取List<Bean>
	 */
	public <T> List<T> queryListBean(String sql,Class<T> clazz)throws CxdException{
		List<Map<String, Object>> listMap =  this.queryListMap(sql);
		List<T> li = new ArrayList<T>();
		for(Map<String, Object> map:listMap){
			li.add(BUtils.tableMapToBean(map,clazz));
		}
		return li;
	}
	public <T> List<T> queryListBean(String tableName,String column ,String value,Class<T> clazz)throws CxdException{
		return queryListBean(tableName, new String[]{column} , new String[]{value}, clazz,null);
	}
	public <T> List<T> queryListBean(String tableName,String[] columns ,Object[] values,Class<T> clazz)throws CxdException{
		return queryListBean(tableName, columns , values, clazz,null);
	}
	public <T> List<T> queryListBean(String tableName,String[] columns ,Object[] values,Class<T> clazz,String sort)throws CxdException{
		List<Map<String, Object>> listMap = this.getListMap(tableName,columns,values,sort);
		if(listMap.size() == 0){
			return null;
		}
		List<T> li = new ArrayList<T>();
		for(Map<String, Object> map:listMap){
			li.add(BUtils.tableMapToBean(map,clazz));
		}
		return li;
	}

	/**
	 * 参数中不允许有的字符串
	 */
	static List<String> noCanHaveParaList = new ArrayList<String>();
	static{
		noCanHaveParaList.add("--");
		noCanHaveParaList.add("'");
		noCanHaveParaList.add("\"");
		noCanHaveParaList.add(";");
		noCanHaveParaList.add("update ");
		noCanHaveParaList.add("delete ");
		noCanHaveParaList.add("or ");
		noCanHaveParaList.add("drop ");
		noCanHaveParaList.add("into ");
		noCanHaveParaList.add("insert ");
		noCanHaveParaList.add("and ");
	}
	/**
	 * 防止sql注入,参数过滤
	 * <一句话功能简述> <功能详细描述>
	 * @author wtj
	 * @date 2015年12月10日下午8:19:11
	 * @param para
	 */
	public static String filterSqlPara(Object para){
		if(para == null){
			throw new CxdException("参数不允许为空");
		}
		String paraStr = para.toString();
		for(String str:noCanHaveParaList){
			if(paraStr.toString().indexOf(str)>=0){
				throw new CxdException("参数【"+paraStr+"】存在风险,请联系管理员");
			}
		}
		return paraStr.trim();
	}
	public int queryForInt(String sql){
		return rmanageMapper.queryForInt(sql);
	}
	public List<Map<String, Object>> queryListMap(String sql){
		return rmanageMapper.queryForList(sql);
	}
	public Map<String,Object> queryMap(String sql){
		return rmanageMapper.queryForMap(sql);
	}
	public int update(String sql){
		return wmanageMapper.modify(sql);
	}
}